package org.jeecg.modules.quartz.job;

import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;

import java.text.SimpleDateFormat;
import java.time.YearMonth;
import java.util.Calendar;
import java.util.Date;
import java.util.UUID;

/**
 * 房间费用明细-定时任务
 */
public class SgRoomFeeDetailJob  implements Job {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public void execute(JobExecutionContext jobExecutionContext) throws JobExecutionException {
        //获得当前月份
//        String month = YearMonth.now().toString();
        //获取上月
        String month = getLastMonth();
        //----计算入住天数---------
        //1、删除当前月份批次的人员明细
        String delete_sg_person_fee_detail="delete from sg_person_fee_detail where month='"+month+"'";
        jdbcTemplate.execute(delete_sg_person_fee_detail);
        //2、计算入住天数
        StringBuilder insert_sg_person_fee_detail=new StringBuilder();
        insert_sg_person_fee_detail.append("insert into sg_person_fee_detail (");
        insert_sg_person_fee_detail.append("id,person_id,idcard,room_id,berth_id,in_room_date,out_room_date,in_room_days,status,month");
        insert_sg_person_fee_detail.append(") select ");
        insert_sg_person_fee_detail.append("uuid(),id,idcard,room_id,berth_id,in_room_date,out_room_date,in_room_days,status,'"+month+"'");
        insert_sg_person_fee_detail.append(" from v_sg_person_room_berth_days");
        jdbcTemplate.execute(insert_sg_person_fee_detail.toString());
        //----计算入住费用---------start
        //1、先删除本月的费用数据
        String delete_sg_room_fee_detail="delete from sg_room_fee_detail where month='"+month+"'";
        jdbcTemplate.execute(delete_sg_room_fee_detail);
        //2、将维护好的抄表复制到当前表中
        StringBuilder insert_sg_room_fee_detail=new StringBuilder();
        insert_sg_room_fee_detail.append("insert into sg_room_fee_detail(");
        insert_sg_room_fee_detail.append("id,month,room_id,last_hot_water,now_hot_water,last_cool_water,");
        insert_sg_room_fee_detail.append("now_cool_water,last_power,now_power");
        insert_sg_room_fee_detail.append(") select ");
        insert_sg_room_fee_detail.append("id,month,room_id,last_hot_water,now_hot_water,last_cool_water,");
        insert_sg_room_fee_detail.append("now_cool_water,last_power,now_power");
        insert_sg_room_fee_detail.append(" from sg_check_meter where month='"+month+"' ");
        jdbcTemplate.execute(insert_sg_room_fee_detail.toString());
        //3、将计算好的房间天数赋值到房间里
        StringBuilder update_sg_room_fee_detail_in_room_days=new StringBuilder();
        update_sg_room_fee_detail_in_room_days.append("update sg_room_fee_detail set ");
        update_sg_room_fee_detail_in_room_days.append("in_room_days=(select in_room_days from v_sg_person_month where v_sg_person_month.room_Id=sg_room_fee_detail.room_id)");
        update_sg_room_fee_detail_in_room_days.append("where month='"+month+"'");
        jdbcTemplate.execute(update_sg_room_fee_detail_in_room_days.toString());
        //4、更新水电费单价
        StringBuilder update_sg_room_fee_detail_price=new StringBuilder();
        update_sg_room_fee_detail_price.append(" UPDATE sg_room_fee_detail");
        update_sg_room_fee_detail_price.append(" INNER JOIN sg_price ON sg_room_fee_detail.`month` = sg_price.`month` ");
        update_sg_room_fee_detail_price.append(" SET sg_room_fee_detail.hot_water_price = sg_price.cool_water,");
        update_sg_room_fee_detail_price.append(" sg_room_fee_detail.cool_water_price = sg_price.hot_water,");
        update_sg_room_fee_detail_price.append(" sg_room_fee_detail.power_fee_price = sg_price.power");
        update_sg_room_fee_detail_price.append(" where sg_room_fee_detail.month='"+month+"'");
        jdbcTemplate.execute(update_sg_room_fee_detail_price.toString());
        //5.1、计算每个房间的费用-更新水电费用量
        StringBuilder update_sg_room_fee_detail=new StringBuilder();
        update_sg_room_fee_detail.append(" UPDATE sg_room_fee_detail SET  ");
        update_sg_room_fee_detail.append(" use_hot_water=now_hot_water - last_hot_water, ");
        update_sg_room_fee_detail.append(" use_cool_water=now_cool_water - last_cool_water, ");
        update_sg_room_fee_detail.append(" user_power=now_power - last_power  ");
        update_sg_room_fee_detail.append(" where month='"+month+"'");
        jdbcTemplate.execute(update_sg_room_fee_detail.toString());
        //5.2、计算每个房间的费用-更新平均水电用量
        update_sg_room_fee_detail=new StringBuilder();
        update_sg_room_fee_detail.append("update sg_room_fee_detail set ");
        update_sg_room_fee_detail.append("average_hot_water=use_hot_water/in_room_days,");
        update_sg_room_fee_detail.append("average_cool_water=use_cool_water/in_room_days,");
        update_sg_room_fee_detail.append("average_power=user_power/in_room_days");
        update_sg_room_fee_detail.append(" where month='"+month+"'");
        jdbcTemplate.execute(update_sg_room_fee_detail.toString());
        //5.3、计算每个房间的费用-更新水电费
        update_sg_room_fee_detail=new StringBuilder();
        update_sg_room_fee_detail.append("update sg_room_fee_detail set ");
        update_sg_room_fee_detail.append("hot_water_fee=use_hot_water*hot_water_price,");
        update_sg_room_fee_detail.append("cool_water_fee=use_cool_water*cool_water_price,");
        update_sg_room_fee_detail.append("power_fee=user_power*power_fee_price");
        update_sg_room_fee_detail.append("  where month='"+month+"'");
        jdbcTemplate.execute(update_sg_room_fee_detail.toString());
        //5.4、计算每个房间的费用-平均水电费
        update_sg_room_fee_detail=new StringBuilder();
        update_sg_room_fee_detail.append("update sg_room_fee_detail set ");
        update_sg_room_fee_detail.append("average_hot_water_fee=average_hot_water*hot_water_price,");
        update_sg_room_fee_detail.append("average_cool_water_fee=average_cool_water*cool_water_price,");
        update_sg_room_fee_detail.append("average_power_fee=average_power*power_fee_price");
        update_sg_room_fee_detail.append(" where month='"+month+"'");
        jdbcTemplate.execute(update_sg_room_fee_detail.toString());
        //5.5、计算每个房间的费用-总费用
        update_sg_room_fee_detail=new StringBuilder();
        update_sg_room_fee_detail.append("update sg_room_fee_detail set ");
        update_sg_room_fee_detail.append("fee_total=hot_water_fee+cool_water_fee+power_fee,");
        update_sg_room_fee_detail.append("average_fee_total=average_hot_water_fee+average_cool_water_fee+average_power_fee");
        update_sg_room_fee_detail.append(" where month='"+month+"'");
        jdbcTemplate.execute(update_sg_room_fee_detail.toString());
        //6.1、计算每个人在房间的费用-将水电费平均数更新到人员表中
        StringBuilder update_sg_person_fee_detail=new StringBuilder();
        update_sg_person_fee_detail.append("UPDATE sg_person_fee_detail");
        update_sg_person_fee_detail.append(" INNER JOIN sg_room_fee_detail ON sg_person_fee_detail.room_id = sg_room_fee_detail.room_id  SET");
        update_sg_person_fee_detail.append(" sg_person_fee_detail.cool_fee = sg_room_fee_detail.average_hot_water * sg_person_fee_detail.in_room_days,");
        update_sg_person_fee_detail.append(" sg_person_fee_detail.hot_fee = sg_room_fee_detail.average_cool_water * sg_person_fee_detail.in_room_days,");
        update_sg_person_fee_detail.append(" sg_person_fee_detail.power_fee = sg_room_fee_detail.average_power * sg_person_fee_detail.in_room_days ");
        update_sg_person_fee_detail.append(" where sg_person_fee_detail.month='"+month+"'");
        jdbcTemplate.execute(update_sg_person_fee_detail.toString());
        //6.2、计算每个人需要缴纳的费用-清空上个月费用重新汇算
        StringBuilder delete_sg_person_month_fee=new StringBuilder();
        delete_sg_person_month_fee.append("delete from   sg_person_month_fee_detail where fee_month='"+month+"'");
        jdbcTemplate.execute(delete_sg_person_month_fee.toString());
        //6.3、计算每个人需要缴纳的费用-根据人员合并费用
        StringBuilder insert_sg_person_month_fee=new StringBuilder();
        insert_sg_person_month_fee.append("insert into  sg_person_month_fee_detail select * from v_sg_person_month_fee_detail");
        jdbcTemplate.execute(insert_sg_person_month_fee.toString());
    }
    /**
     * 获取上个月月份
     * @return
     */
    public static final String getLastMonth() {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM");
        Date date = new Date();
        Calendar calendar = Calendar.getInstance();
        // 设置为当前时间
        calendar.setTime(date);
        calendar.add(Calendar.MONTH,-1);
        // 设置为上一个月
        //calendar.set(Calendar.MONTH, calendar.get(Calendar.MONTH) - 1);
        date = calendar.getTime();
        return format.format(date);
    }
}
